__version__ = "$Revision: 127 $"
__date__ = "$Date: 2011-07-31 22:12:28 +0200 (So, 31 Jul 2011) $"
__lastChangedBy__ = "$Author: wolters@3sconsult.de $"
__repository__ = "$HeadURL: https://sir3spy.googlecode.com/svn/Sir3sPy/Utils/trunk/Db.py $"

"""Module Db.

Description
-----------
    - Class(es): 
        -- DbOra
        -- DbAdo (just for adodbapi demonstration)
"""

import os
import sys
import logging
from optparse import OptionParser,OptionGroup 

#DbOra
import cx_Oracle

#DbAdo
import adodbapi
import re # WHERE attr>:attrParam ==> WHERE attr>?

class DbOra:
    """Provides functions for typical database tasks. For Oracle Databases.

    Description
    -----------
         - __init__: The database is specified by a connectionString - DbOra(connectionString). 
         - isConnectionOk: True, if a connection to the database is possible.
         - createDatabase: True, if the database was created (or connection is already possible).
         - deleteDatabase: True, if the database was deleted (or connection is already impossible). 
         - executeCommand: True, if the SQL command was executed in the database successfully. 
         - executeCommandScript: >0, if a SQL command script was executed (with SQLPlus) in the database successfully.      
         - executeSelect: Returns colNames and data of the select command executed in the database. 
         - executeSelectScript: Returns colNames and data of the select command in the Script executed in the database. 
    """    
    
    connectionStringDefault='myDb/myPw@xe' 
    connectionStringAdminDefault='system/sir3s@xe' 
          
    def __init__(self,connectionString=None):
        """The database is specified by a connectionString - DbOra(connectionString).
        
        Parameter
        ---------
            <connectionString>    
                - examples: 
                    - user/password@server
                    - myDb/myPw@xe                      
        """           

        fileName =__file__                
        logName=fileName[__file__.find('Sir3sPy'):]+'.'+self.__class__.__name__+'.__init__'
        logName=logName.replace('.py','').replace('\\','.').replace('/','.')  
        logger = logging.getLogger(logName) 
        logger.debug('Start')             
        
        try:
            if isinstance(connectionString,str):
                self.connectionString=connectionString
            else:
                self.connectionString=self.connectionStringDefault                                                     
        except:
            logger.error('Exception!')                 
            raise
        finally:
            logger.debug('_Done: self.connectionString: {:s}'.format(self.connectionString))                                       

    def isConnectionOk(self):
        """True, if a connection to the database is possible.

        Return
        ------
            True or False                                                 
        """

        fileName =__file__
        logName=fileName[__file__.find('Sir3sPy'):]+'.'+self.__class__.__name__+'.isConnectionOk'
        logName=logName.replace('.py','').replace('\\','.').replace('/','.')  
        logger = logging.getLogger(logName)              
        logger.debug('Start') 
        
        returnValue=None        
        try:        
            # get connection
            con = cx_Oracle.connect(self.connectionString)
            # test connection
            cur = con.cursor()    
            # close connection
            cur.close()
            returnValue=True    
        except cx_Oracle.DatabaseError as exc:     
            errorMsg, =  exc.args
            errorMsg = str(errorMsg).rstrip('\n')     
            logger.warning('cx_Oracle.DatabaseError: {:s}'.format(errorMsg))
            returnValue=False                 
        except:
            logger.error('Exception!')
            raise   
        finally:
            logger.debug('_Done: self.connectionString: {:s}: {:d}'.format(self.connectionString,returnValue)) 
            return returnValue       

    def createDatabase(self,connectionStringAdmin=None):
        """True, if the database was created (or connection is already possible).

        Parameter
        ---------
            <connectionStringAdmin>                                     

        Return
        ------
            True, if the database was created (or connection is already possible). 
            False, otherwise.                                                 
        """

        fileName =__file__
        logName=fileName[__file__.find('Sir3sPy'):]+'.'+self.__class__.__name__+'.createDatabase'
        logName=logName.replace('.py','').replace('\\','.').replace('/','.')  
        logger = logging.getLogger(logName)     
        logger.debug('Start')         
                
        returnValue=None        
        try:        
            if self.isConnectionOk():
                logger.warning('Database: self.connectionString: {:s}: connection is already possible.'.format(self.connectionString)) 
                returnValue=True
            else:

                if isinstance(connectionStringAdmin,str):
                    pass
                else:
                    connectionStringAdmin=self.connectionStringAdminDefault 
                logger.debug('self.connectionString: {:s} connectionStringAdmin: {:s}'.format(self.connectionString,connectionStringAdmin)) 
                
                # connect as admin
                con = cx_Oracle.connect(connectionStringAdmin)
                cur = con.cursor()  
                
                # create TS, DF, USER
                user = self.connectionString[0:self.connectionString.find('/')] 
                password = self.connectionString[self.connectionString.find('/')+1:self.connectionString.find('@')]
                ts = 'TS_'+user
                df = 'DF_'+user
                
                sqlCmd="CREATE TABLESPACE "+ts+" DATAFILE "+"'"+df+"'"+" SIZE 100M AUTOEXTEND ON NEXT 20M" 
                logger.debug('sqlCmd: {:s}'.format(sqlCmd))               
                cur.execute(sqlCmd)
                
                sqlCmd="CREATE USER "+user+" IDENTIFIED BY "+'"'+password+'"'+" DEFAULT TABLESPACE "+ts                
                logger.debug('sqlCmd: {:s}'.format(sqlCmd))   
                cur.execute(sqlCmd)
                
                sqlCmd="GRANT CONNECT TO "+user            
                logger.debug('sqlCmd: {:s}'.format(sqlCmd))                   
                cur.execute(sqlCmd)
                
                sqlCmd="GRANT RESOURCE TO "+user              
                logger.debug('sqlCmd: {:s}'.format(sqlCmd))   
                cur.execute(sqlCmd)
                
                sqlCmd="GRANT CREATE ANY VIEW TO "+user                
                logger.debug('sqlCmd: {:s}'.format(sqlCmd))   
                cur.execute(sqlCmd)
                
                sqlCmd="GRANT DBA TO "+user                
                logger.debug('sqlCmd: {:s}'.format(sqlCmd))   
                cur.execute(sqlCmd)
                                
                # close connection
                cur.close()
                con.commit()
                returnValue=True    
        except cx_Oracle.DatabaseError as exc:     
            errorMsg, =  exc.args
            errorMsg = str(errorMsg).rstrip('\n')     
            logger.error('cx_Oracle.DatabaseError: {:s}'.format(errorMsg))
            returnValue=False                 
        except:
            logger.error('Exception!')
            raise   
        finally:
            logger.debug('_Done: self.connectionString: {:s}: {:d}'.format(self.connectionString,returnValue)) 
            return returnValue                                     

    def deleteDatabase(self,connectionStringAdmin=None):
        """True, if the database was deleted (or connection is already impossible).   

        Parameter
        ---------
            <connectionStringAdmin>                                     

        Return
        ------
            True, if the database was deleted (or connection is already impossible). 
            False, otherwise.                                                 
        """

        fileName =__file__
        logName=fileName[__file__.find('Sir3sPy'):]+'.'+self.__class__.__name__+'.deleteDatabase'
        logName=logName.replace('.py','').replace('\\','.').replace('/','.')  
        logger = logging.getLogger(logName)      
        logger.debug('Start')                   
               
        returnValue=None        
        try:        
            if not self.isConnectionOk():
                logger.warning('Database: self.connectionString: {:s}: is connection is already impossible.'.format(self.connectionString)) 
                returnValue=True
            else:

                if isinstance(connectionStringAdmin,str):
                    pass
                else:
                    connectionStringAdmin=self.connectionStringAdminDefault
                logger.debug('self.connectionString: {:s} connectionStringAdmin: {:s}'.format(self.connectionString,connectionStringAdmin)) 
                
                # connect as admin
                con = cx_Oracle.connect(connectionStringAdmin)
                cur = con.cursor()  
                
                # delete TS, DF, USER
                user = self.connectionString[0:self.connectionString.find('/')]                 
                ts = 'TS_'+user                
                
                sqlCmd="DROP TABLESPACE "+ts+" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS"
                logger.debug('sqlCmd: {:s}'.format(sqlCmd))               
                cur.execute(sqlCmd)
                
                sqlCmd="DROP USER "+user+" CASCADE"             
                logger.debug('sqlCmd: {:s}'.format(sqlCmd))   
                cur.execute(sqlCmd)
                                                
                # close connection
                cur.close()
                con.commit()
                returnValue=True    
        except cx_Oracle.DatabaseError as exc:     
            errorMsg, =  exc.args
            errorMsg = str(errorMsg).rstrip('\n')     
            logger.error('cx_Oracle.DatabaseError: {:s}'.format(errorMsg))
            returnValue=False                 
        except:
            logger.error('Exception!')
            raise   
        finally:
            logger.debug('_Done: self.connectionString: {:s}: {:d}'.format(self.connectionString,returnValue)) 
            return returnValue                                     

    def executeCommand(self,sqlCommand=None):
        """True, if the SQL command was executed in the database successfully. 
                                                                     
        Parameter
        ---------
            <sqlCommand>                                     

        Return
        ------
            True, if the SQL command was executed in the database successfully.  
            False, otherwise. 
        """

        fileName =__file__
        logName=fileName[__file__.find('Sir3sPy'):]+'.'+self.__class__.__name__+'.executeCommand'
        logName=logName.replace('.py','').replace('\\','.').replace('/','.')  
        logger = logging.getLogger(logName)        
        logger.debug('Start')   
                    
        returnValue = None
        try:
            if sqlCommand != None:
                if isinstance(sqlCommand,str):                
                    logger.debug('self.connectionString: {:s} sqlCommand: {:s}'.format(self.connectionString,sqlCommand))                             
                else:
                    returnValue = False
                    return                                                             
            else:
                returnValue = False
                return                

            # get data from select
            con = cx_Oracle.connect(self.connectionString)
            cur = con.cursor()          
            cur.execute(sqlCommand)             
            cur.close()
            con.commit()
            returnValue=True
    
        except cx_Oracle.DatabaseError as exc:   
            returnValue = False
            errorMsg, =  exc.args
            errorMsg = str(errorMsg).rstrip('\n')     
            logger.error('cx_Oracle.DatabaseError: {:s}'.format(errorMsg))                 
        except:
            logger.error('Exception!')
            raise   
        finally:
            logger.debug('_Done: self.connectionString: {:s}: {:d}'.format(self.connectionString,returnValue))                                                
            return returnValue

    def executeCommandScript(self,sqlCommandScript=None):
        """>0, if a SQL command script was executed (with SQLPlus) in the database successfully.

        Parameter
        ---------
            <sqlCommandScript>
                - exit in the Script necessary: Otherwise SQLPlus will not return 
                - spool in the Script to baseName.log file necessary: Otherwise no log file will be produced
                - see executeCommandScriptExample....sql for examples concerning exit and spool    
            
        Return
        ------
            - >0, if
                  - execution of <sqlCommandScript> successfull
                    - 1 SQLPlus returned 0 after executing <sqlCommandScript>
                        --- log file (if exists) is deleted
            - <0, otherwise                  
                   - -1 SQLPlus returned <>0 after executing <sqlCommandScript>
                        --- log file (if exists) is not deleted
                   - -2 <sqlCommandScript> argument is missing
                   - -3 <sqlCommandScript> is not str type                  
                   - -4 <sqlCommandScript> has not the extension .sql 
                   - -5 <sqlCommandScript> does not exist      
                   - -6 <sqlCommandScript> is not readable  
                   - -7 dir of <sqlCommandScript> is not writable                                                   
        """

        fileName =__file__
        logName=fileName[__file__.find('Sir3sPy'):]+'.'+self.__class__.__name__+'.executeCommandScript'
        logName=logName.replace('.py','').replace('\\','.').replace('/','.')  
        logger = logging.getLogger(logName)  
        logger.debug('Start')                       
                    
        returnValue=None
        try:
            #<sqlCommandScript>
            if sqlCommandScript != None:
                if isinstance(sqlCommandScript,str):                
                    logger.debug('self.connectionString: {:s} sqlCommandScript: {:s}'.format(self.connectionString,sqlCommandScript))         
                    sqlCommandScript=sqlCommandScript.strip()
                    sqlCommandScript=sqlCommandScript.strip('"')           
                    logDir,sqlFilename=os.path.split(sqlCommandScript)
                    if len(logDir)==0: # sqlCommandScript contains no absolute or relative path information
                        logDir=os.getcwd()                     
                    baseName,ext=os.path.splitext(sqlFilename)   
                    logFile=os.path.join(logDir,baseName)+'.log'
                else:
                    returnValue=-3
                    return                                                             
            else:
                returnValue=-2
                return                
                                  
            if ext != '.sql':
                returnValue=-4
                return
                
            if not os.path.exists(sqlCommandScript):
                returnValue=-5
                return

            if not os.access(sqlCommandScript,os.R_OK):
                returnValue=-6
                return

            if not os.access(logDir,os.W_OK):
                returnValue=-7
                return

            # sqlcmd command execution
            sqlcmd = 'sqlplus '+self.connectionString+' '+'@'+baseName            
            logger.debug('sqlcmd: {:s}'.format(sqlcmd)) 
            sqlcmdReturnValue = os.system(sqlcmd) #exit in the script necessary
            if sqlcmdReturnValue==0:
                returnValue=1
                if os.path.exists(logFile): #spool in the script necessary                                     
                    os.remove(logFile)        
            else:                
                returnValue=-1
                                              
        except:
            logger.error('Exception!')          
            raise
        finally:
            logger.debug('_Done: self.connectionString: {:s}: {:d}'.format(self.connectionString,returnValue))                                    
            return returnValue         

    def executeSelect(self,sqlSelectCommand=None,**sqlSelectParams):
        """Returns colNames and data of the select command executed in the database.
                                                                     
        Parameter
        ---------
            <sqlSelectCommand>                                     
            <sqlSelectParams>    

        Return
        ------
            - colNames
                 a list with column names
            - data
               - a list (the rows) of tuples (the column data)  
        """

        fileName =__file__
        logName=fileName[__file__.find('Sir3sPy'):]+'.'+self.__class__.__name__+'.executeSelect'
        logName=logName.replace('.py','').replace('\\','.').replace('/','.')  
        logger = logging.getLogger(logName)  
        logger.debug('Start')   
                    
        colNames = None
        data = None    
        try:
            if sqlSelectCommand != None:
                if isinstance(sqlSelectCommand,str):                
                    logger.debug('self.connectionString: {:s} sqlSelectCommand: {:s}'.format(self.connectionString,sqlSelectCommand.replace('\n',' ')))                             
                else:                    
                    return                                                             
            else:                
                return                

            # get data from select
            con = cx_Oracle.connect(self.connectionString)
            cur = con.cursor()          
            cur.execute(sqlSelectCommand,sqlSelectParams)             
            # colNames
            colNames=[]
            for col in cur.description:
                colName=col[0]
                colNames.append(colName)  
            # data
            data = cur.fetchall()
            cur.close()       
            con.close() 
    
        except cx_Oracle.DatabaseError as exc:
            errorMsg, =  exc.args
            errorMsg = str(errorMsg).rstrip('\n')     
            logger.error('DatabaseError: {:s}'.format(errorMsg))                 
        except:
            logger.error('Exception!')
            raise   
        finally:
            if data==None:
                numOfRows=-1
            else:
                numOfRows=len(data)
            
            logger.debug('_Done: self.connectionString: {:s} sqlSelectCommand: {:s} numOfRows: {:d}'.format(self.connectionString,sqlSelectCommand.replace('\n',' '),numOfRows))                                                
            return colNames,data 

    def executeSelectScript(self,fileWithOneSelectCommand=None,**sqlSelectParams):
        """Returns colNames and data of the select command in the Script executed in the database.       
                                                                     
        Parameter
        ---------
            <fileWithOneSelectCommand>   
            <sqlSelectParams>                                                

        Return
        ------
            - colNames
                 a list with column names
            - data
               - a list (the rows) of tuples (the column data)        
        """

        fileName =__file__
        logName=fileName[__file__.find('Sir3sPy'):]+'.'+self.__class__.__name__+'.executeSelectScript'
        logName=logName.replace('.py','').replace('\\','.').replace('/','.')  
        logger = logging.getLogger(logName) 
        logger.debug('Start')                        
                    
        colNames = None
        data = None
        returnValue=None

        try:
            #<fileWithOneSelectCommand>
            if fileWithOneSelectCommand != None:
                if isinstance(fileWithOneSelectCommand,str):                
                    logger.debug('self.connectionString: {:s} fileWithOneSelectCommand: {:s}'.format(self.connectionString,fileWithOneSelectCommand))         
                    fileWithOneSelectCommand=fileWithOneSelectCommand.strip()
                    fileWithOneSelectCommand=fileWithOneSelectCommand.strip('"')           
                    fileDir,selectFilename=os.path.split(fileWithOneSelectCommand)                    
                    baseName,ext=os.path.splitext(selectFilename)   
                else:
                    returnValue=-3
                    return                                                             
            else:
                returnValue=-2
                return                
                                  
            if ext != '.sql':
                returnValue=-4
                return
                
            if not os.path.exists(fileWithOneSelectCommand):
                returnValue=-5
                return

            if not os.access(fileWithOneSelectCommand,os.R_OK):
                returnValue=-6
                return

            # file lesen        
            f = open(fileWithOneSelectCommand,'r') 
            sqlCmd=f.read()
            f.close()                     
            colNames,data = self.executeSelect(sqlCmd,**sqlSelectParams)
               
        except:
            logger.error('Exception!')
            raise   
        finally:
            if colNames != None:
                returnValue=1
            else:
                returnValue=-1
            logger.debug('_Done: self.connectionString: {:s}: {:d}'.format(self.connectionString,returnValue))                                                
            return colNames,data 

class DbAdo:
    """Provides functions for typical database tasks. Using Ado. Just for adodbapi demonstration.

    Description
    -----------
         - __init__: The database is specified by a Ado connectionString - DbAdo(connectionString).         
         - executeCommand: True, if the SQL command was executed in the database successfully.  
    """    
    
    connectionStringDefault='Driver={Oracle in OraClient11g_home1};Dbq=xe;Uid=myDb;Pwd=myPw' 
            
    def __init__(self,connectionString=None):
        """The database is specified by an Ado connectionString - DbAdo(connectionString).
        
        Parameter
        ---------
            <connectionString>    
                - example: 
                    - Driver={Oracle in OraClient11g_home1};Dbq=xe;Uid=myDb;Pwd=myPw                                      
        """           

        fileName =__file__                
        logName=fileName[__file__.find('Sir3sPy'):]+'.'+self.__class__.__name__+'.__init__'
        logName=logName.replace('.py','').replace('\\','.').replace('/','.')  
        logger = logging.getLogger(logName)  
        logger.debug('Start')   
        
        try:
            if isinstance(connectionString,str):
                self.connectionString=connectionString
            else:
                self.connectionString=self.connectionStringDefault                                         
            logger.debug('connectionString: {:s}'.format(connectionString)) 
        except:
            logger.error('Exception!')                 
            raise
        finally:
            logger.debug('_Done: self.connectionString: {:s}'.format(self.connectionString))   

    def executeSelect(self,sqlSelectCommand=None,*sqlSelectParams):
        """Returns colNames and data of the select command executed in the database.
                                                                     
        Parameter
        ---------
            <sqlSelectCommand>                                     

        Return
        ------
            - colNames
                 a list with column names
            - data
               - a list (the rows) of tuples (the column data)  
        """

        fileName =__file__
        logName=fileName[__file__.find('Sir3sPy'):]+'.'+self.__class__.__name__+'.executeSelect'
        logName=logName.replace('.py','').replace('\\','.').replace('/','.')  
        logger = logging.getLogger(logName) 
        logger.debug('Start')                        
                    
        colNames = None
        data = None    
        try:
            if sqlSelectCommand != None:
                if isinstance(sqlSelectCommand,str):                
                    logger.debug('self.connectionString: {:s} sqlSelectCommand: {:s}'.format(self.connectionString,sqlSelectCommand.replace('\n',' ')))                             
                else:                    
                    return                                                             
            else:                
                return                

            con = adodbapi.connect(self.connectionString)
            cursor=con.cursor()
            cursor.execute(sqlSelectCommand,sqlSelectParams)                                              
            data = cursor.fetchall()
            colNames=[]
            for col in cursor.description:
                colName=col[0]
                colNames.append(colName)  
            cursor.close()
            con.close()
                      
        except:
            logger.error('Exception!')
            raise   
        finally:
            if data==None:
                numOfRows=-1
            else:
                numOfRows=len(data)
            
            logger.debug('_Done: self.connectionString: {:s}: sqlSelectCommand: {:s} numOfRows: {:d}'.format(self.connectionString,sqlSelectCommand.replace('\n',' '),numOfRows))                                                
            return colNames,data 
                                                       
if __name__ == "__main__":

    # logging configuration
    filename=__file__
    loggerName=filename[__file__.find('Sir3sPy'):].replace('.py','').replace('\\','.').replace('/','.')
    logging.getLogger(loggerName).addHandler(logging.NullHandler())
    # configure logfile
    moduleName,ext=os.path.splitext(os.path.basename(__file__))     
    logFileName = os.path.join(os.getcwd(),moduleName+'.log') 
    # configure logging       
    logging.basicConfig(filename=logFileName,filemode='w',level=logging.WARNING,format="%(asctime)s ; %(name)-60s ; %(levelname)-7s ; %(message)s")    
        
    try:
        # logging                            
        logger = logging.getLogger(loggerName)
  
        # default return value
        returnValue=None
        
        # configure command line options ...
        parser = OptionParser(usage="%prog <Database> [-v|-q] -u USER -p PASSWORD -s SERVER [[-l SQLSELECTCMD] [-k SQLSELECTPAR]]")
        
        optGrpLog = OptionGroup(parser,"Log Options")
        optGrpLog.add_option("-v", action="store_true" , dest="verbose", default=True, help="DEBUG log level")
        optGrpLog.add_option("-q", action="store_false", dest="verbose",help="WARNING log level")    
        parser.add_option_group(optGrpLog)
        
        optGrpSrv = OptionGroup(parser, "Server Options")
        optGrpSrv.add_option("-u", "--user", help="i.e. myDb"
                      ,action="store", type="string", dest="user", metavar="USER") 
        optGrpSrv.add_option("-p", "--password", help="i.e. myPw"
                      ,action="store", type="string", dest="password", metavar="PASSWORD") 
        optGrpSrv.add_option("-s", "--server", help="i.e. xe"
                      ,action="store", type="string", dest="server", metavar="SERVER")        
        parser.add_option_group(optGrpSrv)    

        optGrpSql = OptionGroup(parser, "SQL Options")
        optGrpSql.add_option("-l", "--sqlSelectCmd", help='"'+"select 1 from dual where 1=:aParameter"+'"'
                      ,action="store", type="string", dest="sqlSelectCmd", metavar="SQLSELECTCMD") 
        optGrpSql.add_option("-k", "--sqlSelectParams", help='"'+"{'aParameter':1}"+'"'
                      ,action="store", type="string", dest="sqlSelectParams", metavar="SQLSELECTPAR") 
        parser.add_option_group(optGrpSql) 
                
        # parse command line
        (options, argsLeftOver) = parser.parse_args()

        # configure logging from command line
        if options.verbose:                        
            logger.setLevel(logging.DEBUG)
        else:
            logger.setLevel(logging.WARNING)    
  
        logger.debug('Start') 
        
        # check command line
        if len(argsLeftOver)>0:
            print ('option parse: keyless arguments are not defined')
            print ('usage information: '+os.path.basename(sys.argv[0])+' '+'--help')     
            returnValue=-2       
            sys.exit(returnValue)   
        if options.user==None or options.password==None or options.server==None:
            print ('options required: -u USER -p PASSWORD -s SERVER')
            print ('usage information: '+os.path.basename(sys.argv[0])+' '+'--help')     
            returnValue=-3       
            sys.exit(returnValue)  
             
        # Usage of DbOra 
        conStr=options.user+'/'+options.password+'@'+options.server        
        db = DbOra(conStr)
        
        if not db.isConnectionOk():
            logger.error('{:s}: Connection failed'.format(conStr))  
            returnValue=-4  
            sys.exit(returnValue)
        else:
            logger.debug('{:s}: Connection ok'.format(conStr))  
            
        if options.sqlSelectCmd==None: 
            returnValue=1 
            sys.exit(returnValue)
        else:    
            if options.sqlSelectParams!=None:
                p=eval(options.sqlSelectParams)
                colNames,data=db.executeSelect(options.sqlSelectCmd,**p)            
            else:
                p=None # checked in Usage of DbAdo
                colNames,data=db.executeSelect(options.sqlSelectCmd) # call without optional args                                               
            if colNames == None:
                logger.error('{:s}: execution failed.'.format(options.sqlSelectCmd))
                returnValue=-5  
                sys.exit(returnValue)
            else:
                logger.debug('{:s}: execution ok'.format(options.sqlSelectCmd))
                logger.debug('{:s}'.format(colNames))
                for row in data:
                    logger.debug('{:s}'.format(row))
                                
        # Usage of DbAdo 
        conStr='Driver={Oracle in OraClient11g_home1};Dbq='+options.server+';Uid='+options.user+';Pwd='+options.password 
        db = DbAdo(conStr)
        if p == None:
            colNames,data=db.executeSelect(options.sqlSelectCmd)
        else:
            colNames,data=db.executeSelect(re.sub(r':\S+','?',options.sqlSelectCmd),*list(p.values()))
        if colNames == None:
            logger.error('{:s}: execution failed.'.format(options.sqlSelectCmd))
            returnValue=-5  
            sys.exit(returnValue)
        else:
            logger.debug('{:s}: execution ok'.format(options.sqlSelectCmd))
            logger.debug('{:s}'.format(colNames))
            for row in data:
                logger.debug('{:s}'.format(row))
            returnValue=1  
            sys.exit(returnValue)            

    except SystemExit:    
        pass    
    except:
        returnValue=-1
        logger.error('Exception!') 
    finally:                                 
        logger.debug('Done')         
        sys.exit(returnValue)                